const mysql = require('mysql2')
const throwNewError = require('./tools/throwError')
const formatDateToMySQL = require('./tools/formatDateToMySQL')
const tool = require('./tools/tool')
const cache = require('./tools/cache')

let connection = null

function execSQL(SQL, useCache = true) {
    // console.log('SQL >> ', SQL)
    return new Promise((resolve, reject) => {


        let cg = cache.get(SQL)
        if (cg != undefined && useCache) {
            // console.log('跳过查询', cg)
            return resolve(cg)
        }
        connection.query(SQL, (error, results, fields) => {
            if (error) {
                console.log(error)
                reject(error)
                // throwNewError(error)
                // connection.query(`insert into log_sql(\`sql\`, status, results, time) values('${getInsertIntoSQLString(SQL)}',0,${error.toString()},'${(new Date()).toString()}')`)
                return resolve([])
            }
            // connection.query(`insert into log_sql(\`sql\`, status, results, time) values('${getInsertIntoSQLString(SQL)}',1,${JSON.stringify(results)},'${(new Date()).toString()}')`, (error1, results1, field1) => {
            //     console.log(error1)
            // })
            cache.update(SQL, results)

            return resolve(results)
        })
    })
}

function execSQLWithParams(SQL, params = []) {
    // console.log('SQL >> ', SQL, JSON.stringify(params))
    return new Promise((resolve, reject) => {

        let cg = cache.get(`${SQL}${JSON.stringify(params)}`)
        // console.log(cg)
        if (cg != undefined) {
            // console.log('跳过查询', cg)
            return resolve(cg)
        }

        connection.query(SQL, params, (error, results, fields) => {
            if (error) {
                console.error('Database query error:', error);
                // 记录错误日志等操作可以放在这里
                // 注意：这里不再throw新错误，而是使用reject来返回错误给调用者
                return reject(error);
            }
            // 如果需要记录成功执行的日志，可以取消下面注释并根据需要调整
            /*
            connection.query(
                `insert into log_sql(\`sql\`, status, results, time) values(?, 1, ?, ?)`,
                [SQL, JSON.stringify(results), (new Date()).toString()],
                (errorLog, resultsLog, fieldsLog) => {
                    if (errorLog) {
                        console.error('Failed to log SQL execution:', errorLog);
                    }
                }
            );
            */

            cache.update(`${SQL}${JSON.stringify(params)}`, results)

            resolve(results);
        });
    });
}

function logSQL(sql, params = '') {
    return
    console.log(sql, params)
}

module.exports = {
    init(config = {
        host: '',
        user: '',
        password: '',
        database: '',
        port: 0
    }) {
        return new Promise((resolve, reject) => {
            connection = mysql.createConnection(config)
            connection.connect(err => {
                if (err === null) {

                } else {
                    throwNewError(err)
                }
                resolve()
            })
        })
    },
    async exe(sql, useCache = true) {
        return await execSQL(sql, useCache)
    },
    async exeWithParams(sql, params) {
        return await execSQLWithParams(sql, params)
    },
    async getTableColumns(tableName) {
        try {
            return await tool.array.format(await execSQL(`describe \`${tableName}\`;`), (e) => {
                return {
                    name: e.Field,
                    type: e.Type
                }
            })
        } catch (error) {
            throwNewError(`不存在表${tableName}`)
        }
    },
    async insertInto(tableName, values = {}) {
        let re = await this.getTableColumns(tableName);
        let sql = `insert into \`${tableName}\` values(`;
        values.createdAt = formatDateToMySQL(new Date());
        values.updatedAt = formatDateToMySQL(new Date());
        for (let i = 0; i < re.length; i++) {
            let value;
            if (values[re[i].name] == undefined) {
                value = (re[i].type.indexOf('int') != -1) ? 0 : '';
            } else {
                value = values[re[i].name];
            }
            if (re[i].type.indexOf('datetime') != -1) {
                value = new Date(value)
            }
            if (re[i].name == 'createdAt' || re[i].name == 'updatedAt') {

            } else {
                if (!((typeof (value) == 'number' && re[i].type.indexOf('int') != -1) ||
                    (value instanceof Date && re[i].type.indexOf('datetime') != -1) ||
                    (typeof (value) == 'string' && (re[i].type == 'longtext' || re[i].type.indexOf('varchar') != -1)))) {
                    throwNewError(`${re[i].name}的值的类型应为${re[i].type}，而不是${typeof (value)}`);
                }
            }
            if (i) sql += ',';
            if (typeof (value) == 'number') {
                sql += value.toString();
            }
            else if (value instanceof Date) {
                sql += formatDateToMySQL(value) == '' ? `NULL` : `'${formatDateToMySQL(value)}'`
            }
            else {
                sql += "'" + value.toString() + "'";
            }
        }
        sql += ');';
        await this.exe(sql);
        return (await execSQL('select last_insert_id()'))[0]['last_insert_id()'];
    },
    async deleteById(tableName, id) {
        let columns = await this.getTableColumns(tableName)
        for (let i = 0; i < columns.length; i++) {
            if (columns[i].name == 'isDeleted') {
                return await this.exe(`update \`${tableName}\` set isDeleted = 1 where id = ${id}`)
            }
        }
        return await this.exe(`delete from \`${tableName}\` where id = ${id}`)
    },
    async updateById(tableName, id, values = {}) {
        let re = await this.getTableColumns(tableName)
        let sql = `update ${tableName} set `
        delete values.id
        delete values.createdAt
        values.updatedAt = formatDateToMySQL(new Date())
        let c = 0
        for (let i = 0; i < re.length; i++) {
            if (values[re[i].name] == undefined) continue
            let value = values[re[i].name]
            if (re[i].type.indexOf('datetime') != -1) {
                value = new Date(value)
            }
            if (!((typeof (value) == 'number' && re[i].type.indexOf('int') != -1) ||
                (value instanceof Date && re[i].type.indexOf('datetime') != -1) ||
                (typeof (value) == 'string' && (re[i].type == 'longtext' || re[i].type.indexOf('varchar') != -1)))) {
                throwNewError(`${re[i].name}的值的类型应为${re[i].type}，而不是${typeof (value)}`);
            }
            if (c) sql += ', '
            sql += "`" + re[i].name + "` = "
            if (re[i].type.indexOf('int') != -1) {
                sql += value.toString()
            } else if (value instanceof Date) {
                sql += `'${formatDateToMySQL(value)}'`
            } else {
                sql += "'" + value.toString() + "'"
            }
            c++
        }
        sql += ` where id = ${id};`
        return await this.exe(sql)
    },
    async select(sql, format = {}, all = false) {
        if (sql.slice(0, 6) != 'select') throwNewError('SQL语句不是select语句')
        let tableName = sql.match(/from\s+([a-zA-Z_][a-zA-Z0-9_]*)/i)[1]
        if (tableName == undefined) throwNewError('SQL语句中没有表名')
        let columns = await this.getTableColumns(tableName)
        let flag = 0
        for (let i = 0; i < columns.length; i++) {
            if (columns[i].name == 'isDeleted') {
                flag = 1
                break
            }
        }
        let re
        if (flag && all === false) {
            re = await this.exe(sql.replace(/from\s+([a-zA-Z_][a-zA-Z0-9_]*)/i, `from (select * from ${tableName} where isDeleted = 0) as filtered_table`))
        } else {
            re = await this.exe(sql)
        }
        format.createdAt = 'date'
        format.updatedAt = 'date'
        return tool.array.format(re, async (item) => {
            let t = await tool.object.for(item, (k, v) => {
                if (item[k] != undefined) {
                    if (format[k] == 'date') {
                        return formatDateToMySQL(new Date(item[k]))
                    }
                    if (format[k] == 'number') {
                        return parseFloat(item[k])
                    }
                    try {
                        let t = JSON.parse(item[k])
                        if (typeof (t) == 'number') {
                            return item[k]
                        }
                        return t
                    } catch (error) {

                    }
                    return item[k]
                } else {
                    return null
                }
            })
            return t
        })
    },
    async selectWithParams(sql, params, format = {}, all = false) {
        // 检查是否为SELECT语句
        if (!sql.toLowerCase().startsWith('select')) {
            throw new Error('SQL语句不是select语句');
        }

        // 提取表名
        let tableNameMatch = sql.match(/from\s+([a-zA-Z_][a-zA-Z0-9_]*)/i);
        if (!tableNameMatch || tableNameMatch.length < 2) {
            throw new Error('SQL语句中没有表名');
        }
        let tableName = tableNameMatch[1];

        // 获取表的列信息
        let columns = await this.getTableColumns(tableName);
        let flag = columns.some(column => column.name === 'isDeleted');

        let querySql;
        if (flag && all === false) {
            // 如果存在isDeleted列，则添加过滤条件
            querySql = sql.replace(/from\s+([a-zA-Z_][a-zA-Z0-9_]*)/i, `from (select * from ${tableName} where isDeleted = 0) as filtered_table`);
        } else {
            querySql = sql;
        }

        // 执行SQL查询
        let results = await this.exeWithParams(querySql, params);

        // 格式化结果
        format.createdAt = 'date';
        format.updatedAt = 'date';

        return tool.array.format(results, async (item) => {
            return tool.object.for(item, async (k, v) => {
                if (item[k] !== undefined) {
                    if (format[k] === 'date') {
                        return formatDateToMySQL(new Date(item[k]));
                    }
                    if (format[k] === 'number') {
                        return parseFloat(item[k]);
                    }
                    try {
                        let parsed = JSON.parse(item[k]);
                        if (typeof parsed === 'number') {
                            return item[k];
                        }
                        return parsed;
                    } catch (error) {
                        // 解析失败，返回原值
                    }
                    return item[k];
                } else {
                    return null;
                }
            });
        });
    }
}